package com.starbaba.loanuser.service.impl;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.base.common.database.BaseDao;
import com.base.common.exception.AppException;
import com.base.common.log.LogService;
import com.base.common.util.StringUtils;
import com.starbaba.core.web.page.PageRequest;
import com.starbaba.core.web.page.PageResponse;
import com.starbaba.loanuser.model.LoanUserInfo;
import com.starbaba.loanuser.service.LoanUserService;
import com.starbaba.model.dictionary.Constant;
import com.tuanzi.server.loan_base.util.PhoneNumNideUtil;

@SuppressWarnings({ "deprecation", "rawtypes" })
public class LoanUserServiceImpl implements LoanUserService {
	
	private static BaseDao dao = new BaseDao(Constant.POOL_LIFE);

	@Override
	public List<LoanUserInfo> getLoanUserList(PageRequest pageRequest, PageResponse pageResponse, Map<String, Object> paramsMap) {

		int start = pageRequest.getIDisplayStart();
		int length = pageRequest.getIDisplayLength();
		List<LoanUserInfo> dataList = new ArrayList<LoanUserInfo>();

		StringBuffer strSql = new StringBuffer();
		String opType = (String) paramsMap.get("optype");
		if (!StringUtils.isEmpty(opType) && "1".equals(opType)) { // 按注册用户查找

			strSql.append("select a.id AS userid, a.product_id AS prdid, c.channel_name, a.createtime AS regdate,");
			strSql.append("b.mobnum, b.amount, b.petname, b.sex, b.idcard , b.city, b.updatetime, b.platform , b.profession, b.credit, b.sesame_point,");
			strSql.append("b.taobao_shops , b.income, b.education, b.createtime from comm_user a left join loan_user_info b on (a.id = b.userid) ");
			strSql.append("left join comm_product_channel c on (b.channel = c.channel_code) ");
			strSql.append("where 1=1 ");
			fillQueryConditionByUser(paramsMap, strSql);
			strSql.append(" order by b." + pageRequest.getOrderby() + " ");
			strSql.append(pageRequest.getOrder());
		}

		if (!StringUtils.isEmpty(opType) && "2".equals(opType)) { // 按测额度用户查找

			strSql.append("SELECT a.mobnum, a.amount, a.petname, a.sex, a.idcard , a.city, a.updatetime, a.platform , ");
			strSql.append("a.profession, a.credit, a.sesame_point, a.taobao_shops , a.income, a.education, a.createtime,");
			strSql.append("b.id AS userid, b.product_id AS prdid, c.channel_name, b.createtime AS regdate ");
			strSql.append("FROM loan_user_info a LEFT JOIN comm_user b ON a.userid = b.id ");
			strSql.append("left join comm_product_channel c on (a.channel = c.channel_code) ");
			strSql.append("where 1=1 and a.amount is not null ");
			fillQueryCondition(paramsMap, strSql);
			strSql.append(" order by a." + pageRequest.getOrderby() + " ");
			strSql.append(pageRequest.getOrder());
		}

		if (strSql.length() > 0 && start >= 0 && length > 0) {
			strSql.append(" LIMIT " + start + ", " + length);
		}

		try {

			dataList = dao.list(strSql.toString(), LoanUserInfo.class);
			
			// 查询用户最后一次打开时间
			String sql = "select c.userid, max(c.time) lastopendate from stat.stat_user_result c where 1=1 ";

			String lastOpenStartTime = (String) paramsMap.get("lastOpenStartTime");
			String lastOpenEndTime = (String) paramsMap.get("lastOpenEndTime");

			if (!StringUtils.isEmpty(lastOpenStartTime)) {
				sql += " and c.time >= '" + lastOpenStartTime + "'";
			}
			if (!StringUtils.isEmpty(lastOpenEndTime)) {
				sql += " and c.time <= '" + lastOpenEndTime + "'";
			}
			sql += " GROUP BY c.userid ";
			List<LoanUserInfo> userList = dao.list(sql, LoanUserInfo.class);

			for (LoanUserInfo info : dataList) {
				for (LoanUserInfo userInfo : userList) {
					if (info.getUserid().equals(userInfo.getUserid())) {
						info.setLastopendate(userInfo.getLastopendate());
					}
				}
				
				info.setMobnum(PhoneNumNideUtil.hideNum(info.getMobnum()));
				info.setIdcard(PhoneNumNideUtil.hideIdCard(info.getIdcard()));
				info.setPetname(PhoneNumNideUtil.hideName(info.getPetname()));
			}

			StringBuffer countSql = new StringBuffer();
			if (!StringUtils.isEmpty(opType) && "1".equals(opType)) { // 按注册用户查找
				countSql.append("SELECT count(a.id) from comm_user a ");
				countSql.append("LEFT JOIN loan_user_info b ON a.id = b.userid ");
				countSql.append("where 1=1 ");
				fillQueryConditionByUser(paramsMap, countSql);
			}

			if (!StringUtils.isEmpty(opType) && "2".equals(opType)) { // 按测额度用户查找
				countSql.append("SELECT count(a.userid) from loan_user_info a ");
				countSql.append("LEFT JOIN comm_user b ON a.userid = b.id ");
				countSql.append("where 1=1 and a.amount is not null ");
				fillQueryCondition(paramsMap, countSql);
			}
			int count = dao.getInt(countSql.toString());
			pageResponse.setITotalRecords(count);
			pageResponse.setITotalDisplayRecords(count);
		} catch (AppException e) {
			LogService.error(e);
		}
		return dataList;
	}

	/**
	 * 填充查询条件
	 * 
	 * @param paramsMap
	 */
	public void fillQueryCondition(Map<String, Object> paramsMap, StringBuffer strSql) {

		String platform = (String) paramsMap.get("platform");
		String prdid = (String) paramsMap.get("prdid");
		String channel = (String) paramsMap.get("channel");
		String petname = (String) paramsMap.get("petname");
		String idcard = (String) paramsMap.get("idcard");
		String sex = (String) paramsMap.get("sex");
		String city = (String) paramsMap.get("city");
		String profession = (String) paramsMap.get("profession");
		String credit = (String) paramsMap.get("credit");
		String start_sesame_point = (String) paramsMap.get("start_sesame_point");
		String end_sesame_point = (String) paramsMap.get("end_sesame_point");
		String taobao_shops = (String) paramsMap.get("taobao_shops");
		String start_income = (String) paramsMap.get("start_income");
		String end_income = (String) paramsMap.get("end_income");
		String education = (String) paramsMap.get("education");
		String createStartTime = (String) paramsMap.get("createStartTime");
		String createEndTime = (String) paramsMap.get("createEndTime");
		String startTime = (String) paramsMap.get("startTime");
		String endTime = (String) paramsMap.get("endTime");
		String updateStartTime = (String) paramsMap.get("updateStartTime");
		String updateEndTime = (String) paramsMap.get("updateEndTime");
		String mobnum = (String) paramsMap.get("mobnum");

		if (!StringUtils.isEmpty(platform)) {
			strSql.append(" and a.platform = '" + platform + "'");
		}
		if (!StringUtils.isEmpty(prdid)) {
			strSql.append(" and b.product_id = '" + prdid + "'");
		}
		if (!StringUtils.isEmpty(channel)) {
			strSql.append(" and b.channel = " + channel);
		}
		if (!StringUtils.isEmpty(petname)) {
			strSql.append(" and a.petname = '" + petname + "'");
		}
		if (!StringUtils.isEmpty(idcard)) {
			strSql.append(" and a.idcard = '" + idcard + "'");
		}
		if (!StringUtils.isEmpty(sex)) {
			strSql.append(" and a.sex = '" + sex + "'");
		}
		if (!StringUtils.isEmpty(city)) {
			strSql.append(" and a.city = '" + city + "'");
		}
		if (!StringUtils.isEmpty(profession)) {
			strSql.append(" and a.profession = '" + profession + "'");
		}
		if (!StringUtils.isEmpty(credit)) {
			strSql.append(" and a.credit = '" + credit + "'");
		}
		if (!StringUtils.isEmpty(start_sesame_point)) {
			strSql.append(" and a.sesame_point >= " + start_sesame_point);
		}
		if (!StringUtils.isEmpty(end_sesame_point)) {
			strSql.append(" and a.sesame_point <= " + end_sesame_point);
		}
		if (!StringUtils.isEmpty(taobao_shops)) {
			strSql.append(" and a.taobao_shops = '" + taobao_shops + "'");
		}
		if (!StringUtils.isEmpty(start_income)) {
			strSql.append(" and a.income >= " + start_income);
		}
		if (!StringUtils.isEmpty(end_income)) {
			strSql.append(" and a.income <= " + end_income);
		}
		if (!StringUtils.isEmpty(education)) {
			strSql.append(" and a.education = '" + education + "'");
		}
		if (!StringUtils.isEmpty(mobnum)) {
			strSql.append(" and a.mobnum = '" + mobnum + "'");
		}
		if (!StringUtils.isEmpty(createStartTime)) {
			strSql.append(" and b.createtime >= '" + createStartTime + " 00:00:00'");
		}
		if (!StringUtils.isEmpty(createEndTime)) {
			strSql.append(" and b.createtime <= '" + createEndTime + " 23:59:59'");
		}
		if (!StringUtils.isEmpty(startTime)) {
			strSql.append(" and a.createtime >= '" + startTime + " 00:00:00'");
		}
		if (!StringUtils.isEmpty(endTime)) {
			strSql.append(" and a.createtime <= '" + endTime + " 23:59:59'");
		}
		if (!StringUtils.isEmpty(updateStartTime)) {
			strSql.append(" and a.updatetime >= '" + updateStartTime + " 00:00:00'");
		}
		if (!StringUtils.isEmpty(updateEndTime)) {
			strSql.append(" and a.updatetime <= '" + updateEndTime + " 23:59:59'");
		}
	}

	/**
	 * 填充查询条件
	 * 
	 * @param paramsMap
	 * @param strSql
	 */
	public void fillQueryConditionByUser(Map<String, Object> paramsMap, StringBuffer strSql) {

		String platform = (String) paramsMap.get("platform");
		String prdid = (String) paramsMap.get("prdid");
		String channel = (String) paramsMap.get("channel");
		String petname = (String) paramsMap.get("petname");
		String idcard = (String) paramsMap.get("idcard");
		String sex = (String) paramsMap.get("sex");
		String city = (String) paramsMap.get("city");
		String profession = (String) paramsMap.get("profession");
		String credit = (String) paramsMap.get("credit");
		String start_sesame_point = (String) paramsMap.get("start_sesame_point");
		String end_sesame_point = (String) paramsMap.get("end_sesame_point");
		String taobao_shops = (String) paramsMap.get("taobao_shops");
		String start_income = (String) paramsMap.get("start_income");
		String end_income = (String) paramsMap.get("end_income");
		String education = (String) paramsMap.get("education");
		String createStartTime = (String) paramsMap.get("createStartTime");
		String createEndTime = (String) paramsMap.get("createEndTime");
		String startTime = (String) paramsMap.get("startTime");
		String endTime = (String) paramsMap.get("endTime");
		String updateStartTime = (String) paramsMap.get("updateStartTime");
		String updateEndTime = (String) paramsMap.get("updateEndTime");
		String mobnum = (String) paramsMap.get("mobnum");

		if (!StringUtils.isEmpty(platform)) {
			strSql.append(" and a.platform = '" + platform + "'");
		}
		if (!StringUtils.isEmpty(prdid)) {
			strSql.append(" and a.product_id = '" + prdid + "'");
		}
		if (!StringUtils.isEmpty(channel)) {
			strSql.append(" and a.channel = " + channel);
		}
		if (!StringUtils.isEmpty(petname)) {
			strSql.append(" and b.petname = '" + petname + "'");
		}
		if (!StringUtils.isEmpty(idcard)) {
			strSql.append(" and b.idcard = '" + idcard + "'");
		}
		if (!StringUtils.isEmpty(sex)) {
			strSql.append(" and b.sex = '" + sex + "'");
		}
		if (!StringUtils.isEmpty(city)) {
			strSql.append(" and b.city = '" + city + "'");
		}
		if (!StringUtils.isEmpty(profession)) {
			strSql.append(" and b.profession = '" + profession + "'");
		}
		if (!StringUtils.isEmpty(credit)) {
			strSql.append(" and b.credit = '" + credit + "'");
		}
		if (!StringUtils.isEmpty(start_sesame_point)) {
			strSql.append(" and a.sesame_point >= " + start_sesame_point);
		}
		if (!StringUtils.isEmpty(end_sesame_point)) {
			strSql.append(" and a.sesame_point <= " + end_sesame_point);
		}
		if (!StringUtils.isEmpty(taobao_shops)) {
			strSql.append(" and b.taobao_shops = '" + taobao_shops + "'");
		}
		if (!StringUtils.isEmpty(start_income)) {
			strSql.append(" and a.income >= " + start_income);
		}
		if (!StringUtils.isEmpty(end_income)) {
			strSql.append(" and a.income <= " + end_income);
		}
		if (!StringUtils.isEmpty(education)) {
			strSql.append(" and b.education = '" + education + "'");
		}
		if (!StringUtils.isEmpty(createStartTime)) {
			strSql.append(" and a.createtime >= '" + createStartTime + " 00:00:00'");
		}
		if (!StringUtils.isEmpty(createEndTime)) {
			strSql.append(" and a.createtime <= '" + createEndTime + " 23:59:59'");
		}
		if (!StringUtils.isEmpty(mobnum)) {
			strSql.append(" and a.mobnum = '" + mobnum + "'");
		}
		if (!StringUtils.isEmpty(startTime)) {
			strSql.append(" and b.createtime >= '" + startTime + " 00:00:00'");
		}
		if (!StringUtils.isEmpty(endTime)) {
			strSql.append(" and b.createtime <= '" + endTime + " 23:59:59'");
		}
		if (!StringUtils.isEmpty(updateStartTime)) {
			strSql.append(" and b.updatetime >= '" + updateStartTime + " 00:00:00'");
		}
		if (!StringUtils.isEmpty(updateEndTime)) {
			strSql.append(" and b.updatetime <= '" + updateEndTime + " 23:59:59'");
		}
	}
	
	@Override
	public LoanUserInfo getLoanUserInfo(Map<String, Object> paramsMap){
		
		LoanUserInfo userInfo = new LoanUserInfo();
		try {
			String userId = (String)paramsMap.get("userid");
	
			if(!StringUtils.isEmpty(userId)){
				userInfo = dao.get("select * from loan_user_info where userid = ?", LoanUserInfo.class, userId);
				userInfo.setMobnum(PhoneNumNideUtil.hideNum(userInfo.getMobnum()));
				userInfo.setPetname(PhoneNumNideUtil.hideName(userInfo.getPetname()));
				userInfo.setIdcard(PhoneNumNideUtil.hideIdCard(userInfo.getIdcard()));
				userInfo.setAdress(PhoneNumNideUtil.hideAddress(userInfo.getAdress()));
			}
		} catch (Exception e) {
			LogService.error(e);
		}
		return userInfo;
	}

	@Override
	public String exportLoanUserCVS(List<LoanUserInfo> list) {

		StringBuffer sf = new StringBuffer();
		sf.append("平台,产品,渠道,贷款金额,注册时间,最后一次打开时间,新增资料时间,用户ID,手机号码,真实姓名,性别,身份证号,居住城市,职业身份,信用卡,芝麻信用,淘宝购物,收入,学历\r\n");

		for (LoanUserInfo vo : list) {
			sf.append(vo.getPlatform()).append(",");
			if (vo.getPrdid() != null && "8000".equals(vo.getPrdid())) {
				sf.append("菠萝贷主包").append(",");
			} else {
				sf.append("--").append(",");
			}
			sf.append(vo.getChannel_name()).append(",");
			sf.append(vo.getAmount()).append(",");
			sf.append("'" + vo.getRegdate()).append(",");
			sf.append("'" + vo.getLastopendate()).append(",");
			sf.append("'" + vo.getCreatetime()).append(",");
			sf.append(vo.getUserid()).append(",");
			sf.append(vo.getMobnum()).append(",");
			sf.append(vo.getPetname()).append(",");
			if (!StringUtils.isEmpty(vo.getSex())) {
				sf.append(vo.getSex()).append(",");
			} else {
				sf.append("--").append(",");
			}
			sf.append(vo.getIdcard()).append(",");
			sf.append(vo.getCity()).append(",");
			if (!StringUtils.isEmpty(vo.getProfession())) {
				sf.append(vo.getProfession()).append(",");
			} else {
				sf.append("--").append(",");
			}
			if (!StringUtils.isEmpty(vo.getCredit())) {
				sf.append(vo.getCredit()).append(",");
			} else {
				sf.append("--").append(",");
			}
			if (vo.getSesame_point() != null) {
				sf.append(vo.getSesame_point()).append(",");
			} else {
				sf.append("--").append(",");
			}
			if (!StringUtils.isEmpty(vo.getTaobao_shops())) {
				sf.append(vo.getTaobao_shops()).append(",");
			} else {
				sf.append("--").append(",");
			}
			if (vo.getIncome() != null) {
				sf.append(vo.getIncome()).append(",");
			} else {
				sf.append("--").append(",");
			}

			if (StringUtils.isEmpty(vo.getEducation())) {
				sf.append(vo.getEducation()).append(",");
			} else {
				sf.append("--").append(",");
			}
			sf.append("\r\n");
		}
		return sf.toString();
	}
}